<?php
/**
 * getNeedApprovalShifts Function
 *
 * Get data from the Proposed changes table with associated employee, datestamp data from other table.
 *
 * @author Terence Yan <teryan2006@gmail.com>
 *
 */

include_once 'UTerrorcode.php';
include_once 'UTcheckAuth.php';
include_once 'UTDBGlobal.php';

/**
 * @param $authUser string
 * @param $authUserPass string
 * @return array
 */
function getNeedApprovalShifts($authUser, $authUserPass)
{
    global $dbaddress;
    global $dbuser;
    global $dbpassword;
    global $dbdatabasename;

    if (checkAuthorization($authUser, $authUserPass) != true){
        return array(ErrorCode::authFailRead);
    }

    $conn = mysqli_connect($dbaddress, $dbuser, $dbdatabasename, $dbpassword);
    if(!$conn)
    {
        die('Could not connect.');
        return array(ErrorCode::sysError);
    }

    $sql = "SELECT pc.roleID, rs.roleName, pc.dateID, dt.datestamp, pc.empID, ac.accName FROM ProposedChange pc LEFT JOIN Roles rs ON pc.roleID = rs.roleID LEFT JOIN Dates dt ON pc.dateID = dt.dateID LEFT JOIN Account ac ON pc.empID = ac.empID;";
    $result = mysqli_query($conn, $sql);
    if (!$result) {
        return array(ErrorCode::failRead);
    }
    $mutableArray = array();
    $item = array();
    while($row = $result -> fetch_assoc()) {
        $item['roleID'] = $row['roleID'];
        $item['roleName'] = $row['roleName'];
        $item['dateID'] = $row['dateID'];
        $item['datestamp'] = $row['datestamp'];
        $item['origEmpID'] = $row['empdID'];
        $item['origAccName'] = $row['accName'];

        $mutableArray[] = $item;
    }

    if (!mysqli_ping($conn)) {
        $conn = mysqli_connect($dbaddress, $dbuser, $dbdatabasename, $dbpassword);
    }

    $results = array();
    foreach($mutableArray as $element) {
        $sql = "SELECT ac.empID, ac.accName FROM Account ac JOIN ProposedChange pc ON pc.myempID = ac.empID WHERE pc.roleID = ".$element['roleID']." AND pc.empID = ".$element['origEmpID']." AND pc.dateID = ".$element['dateID'].";";
        $result = mysqli_query($conn, $sql);

        while ($row = $result -> fetch_assoc()) {
            $targets[] = array(
                'empID' => $row['empID'],
                'accName' => $row['accName']
            );
        }

        $resultmap = $element;
        $resultmap[] = $targets;
        $results[] = $resultmap;
    }

    return array(
        'code' => ErrorCode::successRead,
        'results' => $results
    );

}
?>